/*
 Navicat Premium Dump SQL

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50744 (5.7.44)
 Source Host           : 127.0.0.1:3306
 Source Schema         : smart_community

 Target Server Type    : MySQL
 Target Server Version : 50744 (5.7.44)
 File Encoding         : 65001

 Date: 17/08/2025 23:08:52
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sc_zone
-- ----------------------------
DROP TABLE IF EXISTS `sc_zone`;
CREATE TABLE `sc_zone` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '分区ID',
  `zone_name` varchar(100) NOT NULL COMMENT '分区名称',
  `zone_code` varchar(50) NOT NULL COMMENT '分区编码',
  `community_id` bigint(20) NOT NULL COMMENT '所属社区ID',
  `property_company_id` bigint(20) NOT NULL COMMENT '所属物业公司ID',
  `zone_type` varchar(50) DEFAULT 'residential' COMMENT '分区类型：residential-住宅区，commercial-商业区，office-办公区，parking-停车区，activity-活动区',
  `zone_color` varchar(20) DEFAULT '#409EFF' COMMENT '分区颜色标识',
  `zone_description` text COMMENT '分区描述',
  `zone_boundary` longtext COMMENT '分区边界坐标数据(JSON格式)',
  `zone_center_x` decimal(10,2) DEFAULT NULL COMMENT '分区中心X坐标',
  `zone_center_y` decimal(10,2) DEFAULT NULL COMMENT '分区中心Y坐标',
  `zone_area` decimal(10,2) DEFAULT NULL COMMENT '分区面积(平方米)',
  `building_count` int(11) DEFAULT '0' COMMENT '包含楼栋数量',
  `household_count` int(11) DEFAULT '0' COMMENT '包含住户数量',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态：1-启用，0-禁用',
  `sort_order` int(11) DEFAULT '0' COMMENT '排序顺序',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建用户ID',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新用户ID',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：1-已删除，0-未删除',
  `version` int(11) DEFAULT '1' COMMENT '版本号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_zone_code` (`zone_code`),
  KEY `idx_community_id` (`community_id`),
  KEY `idx_property_company_id` (`property_company_id`),
  KEY `idx_zone_type` (`zone_type`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_zone_community_property` (`community_id`,`property_company_id`,`status`),
  KEY `idx_zone_type_status` (`zone_type`,`status`,`sort_order`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='社区分区表';

-- ----------------------------
-- Records of sc_zone
-- ----------------------------
BEGIN;
INSERT INTO `sc_zone` (`id`, `zone_name`, `zone_code`, `community_id`, `property_company_id`, `zone_type`, `zone_color`, `zone_description`, `zone_boundary`, `zone_center_x`, `zone_center_y`, `zone_area`, `building_count`, `household_count`, `status`, `sort_order`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (1, '东区', 'ZONE_001', 1, 1, 'residential', '#409EFF', '社区东部住宅区域，包含1-4号楼', NULL, NULL, NULL, NULL, 4, 160, 1, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone` (`id`, `zone_name`, `zone_code`, `community_id`, `property_company_id`, `zone_type`, `zone_color`, `zone_description`, `zone_boundary`, `zone_center_x`, `zone_center_y`, `zone_area`, `building_count`, `household_count`, `status`, `sort_order`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (2, '南区', 'ZONE_002', 1, 1, 'residential', '#67C23A', '社区南部住宅区域，包含5-8号楼', NULL, NULL, NULL, NULL, 4, 160, 1, 2, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone` (`id`, `zone_name`, `zone_code`, `community_id`, `property_company_id`, `zone_type`, `zone_color`, `zone_description`, `zone_boundary`, `zone_center_x`, `zone_center_y`, `zone_area`, `building_count`, `household_count`, `status`, `sort_order`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (3, '西区', 'ZONE_003', 1, 1, 'residential', '#E6A23C', '社区西部住宅区域，包含9-12号楼', NULL, NULL, NULL, NULL, 4, 160, 1, 3, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone` (`id`, `zone_name`, `zone_code`, `community_id`, `property_company_id`, `zone_type`, `zone_color`, `zone_description`, `zone_boundary`, `zone_center_x`, `zone_center_y`, `zone_area`, `building_count`, `household_count`, `status`, `sort_order`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (4, '商业区', 'ZONE_004', 1, 1, 'commercial', '#F56C6C', '社区商业区域，包含商业楼和办公楼', NULL, NULL, NULL, NULL, 3, 0, 1, 4, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
COMMIT;

-- ----------------------------
-- Table structure for sc_zone_building
-- ----------------------------
DROP TABLE IF EXISTS `sc_zone_building`;
CREATE TABLE `sc_zone_building` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联ID',
  `zone_id` bigint(20) NOT NULL COMMENT '分区ID',
  `building_id` bigint(20) NOT NULL COMMENT '楼栋ID',
  `community_id` bigint(20) NOT NULL COMMENT '社区ID',
  `property_company_id` bigint(20) NOT NULL COMMENT '物业公司ID',
  `building_position_x` decimal(10,2) DEFAULT NULL COMMENT '楼栋在分区中的X坐标',
  `building_position_y` decimal(10,2) DEFAULT NULL COMMENT '楼栋在分区中的Y坐标',
  `is_primary` tinyint(1) DEFAULT '0' COMMENT '是否主要楼栋：1-是，0-否',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态：1-启用，0-禁用',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建用户ID',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新用户ID',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：1-已删除，0-未删除',
  `version` int(11) DEFAULT '1' COMMENT '版本号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_zone_building` (`zone_id`,`building_id`),
  KEY `idx_zone_id` (`zone_id`),
  KEY `idx_building_id` (`building_id`),
  KEY `idx_community_id` (`community_id`),
  KEY `idx_property_company_id` (`property_company_id`),
  KEY `idx_status` (`status`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_zone_building_community_property` (`community_id`,`property_company_id`,`status`,`deleted`),
  KEY `idx_zone_building_position` (`zone_id`,`building_position_x`,`building_position_y`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COMMENT='分区楼栋关联表';

-- ----------------------------
-- Records of sc_zone_building
-- ----------------------------
BEGIN;
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (1, 1, 1, 1, 1, 100.00, 100.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (2, 1, 2, 1, 1, 200.00, 100.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (3, 1, 3, 1, 1, 300.00, 100.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (4, 1, 4, 1, 1, 400.00, 100.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (5, 2, 5, 1, 1, 100.00, 200.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (6, 2, 6, 1, 1, 200.00, 200.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (7, 2, 7, 1, 1, 300.00, 200.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (8, 2, 8, 1, 1, 400.00, 200.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (9, 3, 9, 1, 1, 100.00, 300.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (10, 3, 10, 1, 1, 200.00, 300.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (11, 3, 11, 1, 1, 300.00, 300.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (12, 3, 12, 1, 1, 400.00, 300.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (13, 4, 13, 1, 1, 500.00, 150.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (14, 4, 14, 1, 1, 500.00, 250.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
INSERT INTO `sc_zone_building` (`id`, `zone_id`, `building_id`, `community_id`, `property_company_id`, `building_position_x`, `building_position_y`, `is_primary`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`) VALUES (15, 4, 15, 1, 1, 600.00, 200.00, 0, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0, 1);
COMMIT;

-- ----------------------------
-- Table structure for sc_zone_drawing
-- ----------------------------
DROP TABLE IF EXISTS `sc_zone_drawing`;
CREATE TABLE `sc_zone_drawing` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '绘图数据ID',
  `community_id` bigint(20) NOT NULL COMMENT '社区ID',
  `property_company_id` bigint(20) NOT NULL COMMENT '物业公司ID',
  `drawing_data` longtext NOT NULL COMMENT '绘图数据(JSON格式)',
  `canvas_width` int(11) DEFAULT '800' COMMENT '画布宽度',
  `canvas_height` int(11) DEFAULT '600' COMMENT '画布高度',
  `zoom_level` decimal(3,2) DEFAULT '1.00' COMMENT '缩放级别',
  `version` int(11) DEFAULT '1' COMMENT '版本号',
  `is_current` tinyint(1) DEFAULT '1' COMMENT '是否为当前版本：1-是，0-否',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建用户ID',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新用户ID',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：1-已删除，0-未删除',
  PRIMARY KEY (`id`),
  KEY `idx_community_id` (`community_id`),
  KEY `idx_property_company_id` (`property_company_id`),
  KEY `idx_is_current` (`is_current`),
  KEY `idx_version` (`version`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_zone_drawing_community_property_current` (`community_id`,`property_company_id`,`is_current`,`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='分区绘图数据表';

-- ----------------------------
-- Records of sc_zone_drawing
-- ----------------------------
BEGIN;
INSERT INTO `sc_zone_drawing` (`id`, `community_id`, `property_company_id`, `drawing_data`, `canvas_width`, `canvas_height`, `zoom_level`, `version`, `is_current`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`) VALUES (1, 1, 1, '{\"version\":\"1.0\",\"canvas\":{\"width\":800,\"height\":600,\"zoomLevel\":1.0},\"zones\":[{\"id\":1,\"name\":\"东区\",\"color\":\"#409EFF\",\"points\":\"50,50 250,50 250,250 50,250\",\"textX\":150,\"textY\":150,\"buildings\":[1,2,3,4]}],\"buildings\":[{\"id\":1,\"name\":\"1幢\",\"type\":\"residential\",\"x\":100,\"y\":100,\"zoneId\":1},{\"id\":2,\"name\":\"2幢\",\"type\":\"residential\",\"x\":200,\"y\":100,\"zoneId\":1},{\"id\":3,\"name\":\"3幢\",\"type\":\"residential\",\"x\":300,\"y\":100,\"zoneId\":1},{\"id\":4,\"name\":\"4幢\",\"type\":\"residential\",\"x\":400,\"y\":100,\"zoneId\":1}],\"metadata\":{\"lastModified\":\"2025-01-30T10:30:00Z\",\"modifiedBy\":\"user123\",\"communityId\":1,\"propertyCompanyId\":1}}', 800, 600, 1.00, 1, 1, '2025-08-15 17:16:24', '2025-08-15 17:16:24', 1, NULL, 0);
COMMIT;

-- ----------------------------
-- Table structure for sc_zone_statistics
-- ----------------------------
DROP TABLE IF EXISTS `sc_zone_statistics`;
CREATE TABLE `sc_zone_statistics` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '统计ID',
  `zone_id` bigint(20) NOT NULL COMMENT '分区ID',
  `community_id` bigint(20) NOT NULL COMMENT '社区ID',
  `property_company_id` bigint(20) NOT NULL COMMENT '物业公司ID',
  `stat_date` date NOT NULL COMMENT '统计日期',
  `building_count` int(11) DEFAULT '0' COMMENT '楼栋数量',
  `household_count` int(11) DEFAULT '0' COMMENT '住户数量',
  `resident_count` int(11) DEFAULT '0' COMMENT '居民数量',
  `vehicle_count` int(11) DEFAULT '0' COMMENT '车辆数量',
  `parking_space_count` int(11) DEFAULT '0' COMMENT '停车位数量',
  `occupied_parking_count` int(11) DEFAULT '0' COMMENT '已占用停车位数量',
  `property_fee_total` decimal(12,2) DEFAULT '0.00' COMMENT '物业费总额',
  `property_fee_paid` decimal(12,2) DEFAULT '0.00' COMMENT '已缴物业费总额',
  `property_fee_unpaid` decimal(12,2) DEFAULT '0.00' COMMENT '未缴物业费总额',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：1-已删除，0-未删除',
  `version` int(11) DEFAULT '1' COMMENT '版本号',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建用户ID',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新用户ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_zone_date` (`zone_id`,`stat_date`),
  KEY `idx_zone_id` (`zone_id`),
  KEY `idx_community_id` (`community_id`),
  KEY `idx_property_company_id` (`property_company_id`),
  KEY `idx_stat_date` (`stat_date`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_zone_statistics_community_property_date` (`community_id`,`property_company_id`,`stat_date`,`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分区统计表';

-- ----------------------------
-- Records of sc_zone_statistics
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Triggers structure for table sc_zone_building
-- ----------------------------
DROP TRIGGER IF EXISTS `tr_zone_building_insert`;
delimiter ;;
CREATE TRIGGER `smart_community`.`tr_zone_building_insert` AFTER INSERT ON `sc_zone_building` FOR EACH ROW BEGIN
    -- 更新分区楼栋数量
    CALL sp_update_zone_building_count(NEW.zone_id);
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table sc_zone_building
-- ----------------------------
DROP TRIGGER IF EXISTS `tr_zone_building_update`;
delimiter ;;
CREATE TRIGGER `smart_community`.`tr_zone_building_update` AFTER UPDATE ON `sc_zone_building` FOR EACH ROW BEGIN
    -- 更新分区楼栋数量
    CALL sp_update_zone_building_count(NEW.zone_id);
    
    -- 如果楼栋ID发生变化，也需要更新旧分区
    IF OLD.zone_id != NEW.zone_id THEN
        CALL sp_update_zone_building_count(OLD.zone_id);
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table sc_zone_building
-- ----------------------------
DROP TRIGGER IF EXISTS `tr_zone_building_delete`;
delimiter ;;
CREATE TRIGGER `smart_community`.`tr_zone_building_delete` AFTER DELETE ON `sc_zone_building` FOR EACH ROW BEGIN
    -- 更新分区楼栋数量
    CALL sp_update_zone_building_count(OLD.zone_id);
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
